MySQL 最左前缀匹配原则
总之先牢记！！ 索引即数据，数据即索引
联合索引是什么？
在看下面的最左前缀匹配原则之前，先来了解下联合索引是什么。
在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗 B+ 树，那么联合索引的底层也就是一颗 B+ 树，只不过联合索引的 B+ 树节点中存储的是键值。由于构建一棵 B+ 树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。
举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。
可以看到 a 的值是有顺序的，1，1，2，2，3，3，而 b 的值是没有顺序的 1，2，1，4，1，2。
但是我们又可发现 a 在等值的情况下，b 值又是按顺序排列的，但是这种顺序是相对的。这是因为 MySQL 创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。
所以 b = 2 这种查询条件没有办法利用索引。
explain 的 type 有哪些类型
在 MySQL 中，EXPLAIN 语句用于分析查询的执行计划，提供有关查询优化器如何执行查询的信息。在 EXPLAIN 的输出结果中，type 列表示查询使用的访问方法类型，常见的 type 类型有以下几种（按照性能的高低进行排序）：
const: 当查询使用常量条件进行匹配时，例如根据主键或唯一索引进行精确匹配时，使用 const 访问方法。此类查询只返回一行结果。常量条件匹配，性能最高。
eq_ref: 在连接查询中，如果使用了索引来匹配唯一的关联行，则使用 eq_ref 访问方法。通常是通过主键或唯一索引进行关联的情况。唯一索引关联匹配，性能较高。
ref: 当查询使用普通的索引进行匹配时，但不是唯一索引或主键，使用 ref 访问方法。该访问方法性能较好。使用普通索引进行匹配，可能返回多个匹配的行。索引匹配，性能较好。
range: 当查询使用范围条件（例如 BETWEEN、<、>）进行索引扫描时，使用 range 访问方法。使用范围条件进行索引扫描，匹配一定范围内的行。范围索引扫描，性能较好。
index: 当查询使用索引进行全索引扫描时，而不是根据索引进行具体的匹配或范围查询。无需具体匹配或范围查询，但需要扫描整个索引。全索引扫描，性能一般。
all: 当查询进行全表扫描时，不使用索引，使用 all 访问方法。此类查询需要扫描整个表，性能较差。
除了上述常见的 type 类型，还可能存在其他类型，如 unique_subquery、index_subquery、materialized 等，具体的类型会根据查询的条件和索引情况而有所不同。
需要注意的是，查询的 type 类型是评估查询性能和优化的重要指标之一。通常情况下，优化器会尽可能选择更高效的访问方法来执行查询，以提高查询性能。因此，理解查询的 type 类型对于优化查询和索引设计是很有帮助的。
最左前缀匹配原则
MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如 User 表的 name 和 city 加联合索引就是 (name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。
如下：
select * from user where name=xx and city=xx ; -- 可以命中索引
select * from user where name=xx ; -- 可以命中索引
select * from user where city=xx ; -- 无法命中索引
这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY 子句也遵循此规则。
最左前缀匹配的使用
首先创建一个表
create table `staffs` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
KEY `id_name_age_index` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
该表中对 id 列 name 列 age 列建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age）。
最左匹配的限制
最左前缀匹配是指在复合索引中，索引列的顺序非常重要，查询时只能从左到右依次使用索引列进行匹配。当遇到不等于 <> 和范围查询 <、>、<=、>= 时，最左前缀匹配就会失效。
具体来说，对于复合索引 (col1, col2, col3)，以下情况下最左前缀匹配会失效：
不等于 <> 操作符：当查询条件中的某个索引列使用了不等于操作符时，最左前缀匹配将失效。例如，col1 <> 10。
范围查询：当查询条件中的某个索引列使用了范围查询操作符 <、>、<=、>= 时，最左前缀匹配将失效。例如，col2 > 5。但是如果是 col1 > 5，这种则可以使用到索引。具体看下面 遇到范围值匹配时的策略 那节
在这些情况下，数据库无法通过索引直接匹配或定位到满足条件的数据行，而需要回表操作或者使用其他索引（如果有）来进一步筛选数据。
查询优化器的小魔法
通过观察上面的结果图可知，where 后面的查询条件，不论是使用（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使用到了联合索引
可能有同学会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配，却也使用到了联合索引？
这是因为 MySQL 中有查询优化器 explain，所以 sql 语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条 SQL 语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引。
匹配最左边的列
该搜索是遵循最左匹配原则的，通过 key 字段也可知，在搜索过程中使用到了联合索引，且使用的是联合索引中的（id）索引，因为 key_len 字段值为 5，而 id 索引的长度正好为 5（因为 id 为 int 型，允许 null，所以占 5 个字节）。
由于 id 到 name 是从左边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则，通过 key 字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id_name）索引（推理同上，就不展开了）
由于上面三个搜索都是从最左边 id 依次向右开始匹配的，所以都用到了 id_name_age_index 联合索引。
那如果不是依次匹配呢？
通过 key 字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id）索引，从 key_len 字段也可知。因为联合索引树是按照 id 字段创建的，但 age 相对于 id 来说是无序的，只有 id 有序的，所以他只能使用联合索引中的 id 索引。
折中的全表查询：index 类型的全索引扫
select * from staffs where name = 'hhh'
通过观察发现上面 key 字段发现在搜索中也使用了 id_name_age_index 索引，可能许多同学就会疑惑它并没有遵守最左匹配原则，按道理会索引失效，为什么也使用到了联合索引？
create table `staffs` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
KEY `id_name_age_index` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
看上面这个表，我们可以知道，id，name，age 都是索引的一部分，但是如果我们只查询 name，那么就会使用到 index 类型的全索引扫描，因为 name 是无序的，所以只能对整个索引树进行扫描，直到找到符合的某个索引，与 all 不同的是，index 是对所有索引树进行扫描，而 all 是对整个磁盘的数据进行全表扫描。所以它与最左匹配原则无关。
一般只要是某联合索引的一部分，但又不遵循最左匹配原则时，都可能会采用 index 类型的方式扫描，但它的效率远不如最做匹配原则的查询效率高，index 类型类型的扫描方式是从索引第一个字段一个一个的查找，直到找到符合的某个索引，与 all 不同的是，index 是对所有索引树进行扫描，而 all 是对整个磁盘的数据进行全表扫描。（说白了就是索引表相较全文搜索小）
select * from staffs where age = 20
select * from staffs where name = 'hhh' and age = 20
这两个结果跟上面的是同样的道理，由于它们都没有从最左边开始匹配，所以没有用到联合索引，使用的都是 index 全索引扫描。
匹配列前缀满足索引最左前缀
如果 id 是字符型，那么前缀匹配用的是索引，中坠和后缀用的是全表扫描。
select * from staffs where id like 'A%'; -- 前缀都是排好序的，使用的都是联合索引
select * from staffs where id like '%A%'; -- 全表查询
select * from staffs where id like '%A'; -- 全表查询
遇到范围值匹配时的策略
select * from staffs where id > 1 and id < 3;
在匹配的过程中遇到 < > = 号，就会停止匹配，但 id 本身就是有序的，所以通过 possible_keys 字段和 key_len 字段可知，在该搜索过程中使用了联合索引的 id 索引，且进行的是 rang 范围查询。（注意看上图的 type）
1、例子一
select * from staffs where id < 40 and age > 20 and age < 50;
由于不遵循最左匹配原则，且在 id < 4 的范围中，age 是无序的，所以使用的是 index 全索引扫描。
2、例子二
select * from staffs where id < 2 and age > 20 and age < 50;
不遵循最左匹配原则，但在数据库中 id < 2 的只有一条（id），所以在 id < 2 的范围中，age 是有序的，所以使用的是 rang 范围查询。
3、例子三
select * from staffs where age > 20 and age < 50;
不遵循最左匹配原则，而 age 又是无序的，所以进行的全索引扫描。
4、例子四
select * from staffs where id = 1 and age < 50;
准确匹配第一列并范围匹配其他某一列
由于搜索中有 id = 1，所以在 id 范围内 age 是无序的，所以只使用了联合索引中的 id 索引。
高级用法：使用前缀索引
语法：index(field(10))，使用字段值的前 10 个字符建立索引，默认是使用字段的全部内容建立索引。
前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。
实操的难度：在于前缀截取的长度。
我们可以利用
select count(*) / count(distinct left(password,prefixLen));
通过从调整 prefixLen 的值（从 1 自增）查看不同前缀长度的一个平均匹配度，接近 1 时就可以了（表示一个密码的前 prefixLen 个字符几乎能确定唯一一条记录）
References
- 数据库常见知识点总结-最左前缀匹配原则、EXPLAIN 命令详解
- 《InnoDB存储引擎》